<?php
	require_once './Tool/DB/SqlDBManager.class.php';
	require_once './Tool/Common/PrepareInput.php';
	include './Reports/conn.php';
	
	$startDate=$endDate=$centreId="";  
	
	if(!empty($_POST['sortBy'])){
		$sort="client.".$_POST['sortBy'];
		$_SESSION["sortBy"] = $sort;
	}else{
		$sort="client.NRIC";
	}
	$order='asc';	

    if(isset($_REQUEST['startDate'])){ 
        $startDate=prepareInput($_REQUEST['startDate']); 
        $startDate = date('Y-m-d', strtotime($_REQUEST['startDate']));
    }
    if(isset($_REQUEST['endDate'])) { 
        $endDate=prepareInput($_REQUEST['endDate']);
        $endDate = date('Y-m-d', strtotime($_REQUEST['endDate'])); 
    }
    if(isset($_REQUEST['ddlCentre'])){ 
        $centreId=prepareInput($_REQUEST['ddlCentre']); 
    }

    //Validate input
    if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$startDate)){
        echo "<font color=red>Invalid Start Date Format.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$endDate)){
        echo "<font color=red>Invalid End Date Format.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]*$/",$centreId)){
        echo "<font color=red>Invalid Centre ID.</font><br/>";
        exit();
    }

	$beforeDeviationSC100 = 0;
	$beforeDeviationSC95 = 0;
	$beforeDeviationSC90 = 0;
	$beforeDeviationSC85 = 0;
	$beforeDeviationSC80 = 0;
	$beforeDeviationSC75 = 0;
	$beforeDeviationSC70 = 0;
	$beforeDeviationSC65 = 0;
	$beforeDeviationSC60 = 0;
	$beforeDeviationSC55 = 0;
	$beforeDeviationSC50 = 0;
	$beforeDeviationSC45 = 0;
	$beforeDeviationSC40 = 0;
	$beforeDeviationSC35 = 0;
	$beforeDeviationSC30 = 0;
	$beforeDeviationSC25 = 0;
	$beforeDeviationSC20 = 0;
	$beforeDeviationSC15 = 0;
	$beforeDeviationSC10 = 0;
	$beforeDeviationSC5 = 0;
	$beforeDeviationSC0 = 0;

	$beforeDeviationPR100 = 0;
	$beforeDeviationPR95 = 0;
	$beforeDeviationPR90 = 0;
	$beforeDeviationPR85 = 0;
	$beforeDeviationPR80 = 0;
	$beforeDeviationPR75 = 0;
	$beforeDeviationPR70 = 0;
	$beforeDeviationPR65 = 0;
	$beforeDeviationPR60 = 0;
	$beforeDeviationPR55 = 0;
	$beforeDeviationPR50 = 0;
	$beforeDeviationPR45 = 0;
	$beforeDeviationPR40 = 0;
	$beforeDeviationPR35 = 0;
	$beforeDeviationPR30 = 0;
	$beforeDeviationPR25 = 0;
	$beforeDeviationPR20 = 0;
	$beforeDeviationPR15 = 0;
	$beforeDeviationPR10 = 0;
	$beforeDeviationPR5 = 0;
	$beforeDeviationPR0 = 0;

	$afterDeviationSC100 = 0;
	$afterDeviationSC95 = 0;
	$afterDeviationSC90 = 0;
	$afterDeviationSC85 = 0;
	$afterDeviationSC80 = 0;
	$afterDeviationSC75 = 0;
	$afterDeviationSC70 = 0;
	$afterDeviationSC65 = 0;
	$afterDeviationSC60 = 0;
	$afterDeviationSC55 = 0;
	$afterDeviationSC50 = 0;
	$afterDeviationSC45 = 0;
	$afterDeviationSC40 = 0;
	$afterDeviationSC35 = 0;
	$afterDeviationSC30 = 0;
	$afterDeviationSC25 = 0;
	$afterDeviationSC20 = 0;
	$afterDeviationSC15 = 0;
	$afterDeviationSC10 = 0;
	$afterDeviationSC5 = 0;
	$afterDeviationSC0 = 0;

	$afterDeviationPR100 = 0;
	$afterDeviationPR95 = 0;
	$afterDeviationPR90 = 0;
	$afterDeviationPR85 = 0;
	$afterDeviationPR80 = 0;
	$afterDeviationPR75 = 0;
	$afterDeviationPR70 = 0;
	$afterDeviationPR65 = 0;
	$afterDeviationPR60 = 0;
	$afterDeviationPR55 = 0;
	$afterDeviationPR50 = 0;
	$afterDeviationPR45 = 0;
	$afterDeviationPR40 = 0;
	$afterDeviationPR35 = 0;
	$afterDeviationPR30 = 0;
	$afterDeviationPR25 = 0;
	$afterDeviationPR20 = 0;
	$afterDeviationPR15 = 0;
	$afterDeviationPR10 = 0;
	$afterDeviationPR5 = 0;
	$afterDeviationPR0 = 0;

	$countBeforeDeviationSC = 0;
	$countAfterDeviationSC = 0;
	$countAfterDeviationPR = 0;
	$countBeforeDeviationPR = 0;

	$beforeDeviationTotalAverageSC = 0;
	$beforeDeviationTotalAveragePR = 0;
	$afterDeviationTotalAverageSC = 0;
	$afterDeviationTotalAveragePR = 0;

	$sqlDBManager=new SqlDBManager();
	if (!empty($_SESSION['userType'])) {
		$userType = $_SESSION['userType'];
	}
	if ($userType != 'System Admin') {
		$sql = "Select CentreId from Centre where CentreName=?";
		$parameters=array($_SESSION['centreName']);
		$res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$centreId = $res['CentreId'];
		}
	}
	
	if ($centreId == 0) {
		$clwhere = "client.CentreId is not null";
		$where = "CentreId is not null";
		$kwhere = "INNER JOIN centre on client.CentreId= centre.CentreId where client.CentreId is not null order by $sort $order";
	} else {
		$clwhere = "client.CentreId=$centreId";
		$where = "CentreId=$centreId";
		$kwhere = "INNER JOIN centre on client.CentreId=centre.CentreId where client.CentreId=$centreId order by $sort $order";
	}

	//date range
	$range = (strtotime($endDate)- strtotime($startDate))/24/3600;

	$_SESSION["startDate"] = $_REQUEST['startDate'];
	$_SESSION["endDate"] = $_REQUEST['endDate'];
	$_SESSION["centreId"] = $centreId;

	if ($userType != "Therapy Assistant") {
		echo"<form action='Reports/export.php' method='post'>
			<p><input type='submit'button class='btn' id='btnExport'value='Export'onClick='window.location.href='Reports/export.php'></p>
			</form>";
	}

	echo "<div class='tablereport' id='table'style='width:980px;overflow:scroll'>
		<table id = 'tableExcel' cellspacing='2' cellpadding='2' align='center' name='tableatt'>							
		<tr>
			<td>S/No</td>
			<td>NRIC</td>
			<td>Name</td>";
	if($userType=="System Admin"){
		echo "<td>Centre Name</td>";
		}
	echo "<td>Citizenship</td>
		<td>Daycare</td>
		<td>Before Deviation</td>
		<td>After Deviation</td>";

	$sql="select date from holiday";
	$res=$sqlDBManager->queryRows($sql);
	
	$Holiday=array();

	if(!empty($res)){
		for($i=0;$i<count($res);$i++)
		{
			array_push($Holiday, $res[$i]);
		}
	}

	for($i=0;$i<=$range;$i++){
		$date=Date('Y-m-d', strtotime($startDate."+".$i." days"));
		for($j=0;$j<count($Holiday);$j++){
			if($Holiday[$j][0]==Date('Y-m-d', strtotime($startDate."+".$i." days")) || date('l', strtotime(Date('Y-m-d', strtotime($startDate."+".$i." days"))))== "Sunday" || date('l', strtotime(Date('Y-m-d', strtotime($startDate."+".$i." days"))))== "Saturday"){
				$i++;
			}		
		}
		$realDate=Date('d/m/Y', strtotime($startDate."+".$i." days"));
 		echo "<td>" . $realDate . "</td>";	
	}

	echo "<td>Total</td></tr>";
	$ttlattendance = 0;
	$sql = "SELECT * from Client $kwhere";
	$result = mysql_query($sql,$conn);
	$sno = 1;
	while ($row = mysql_fetch_array($result)) {
		echo "
				<tr>
				<td>
				" . $sno . "</td>
				<td>
				" . $row['NRIC'] . "</td>
				<td>
				" . $row['CustomerName'] . "</td>";
								if($userType=="System Admin"){
								echo "
						   <td>
				" . $row['CentreName'] . "</td>";}
								echo "
				<td>
				" . $row['Citizenship'] . "</td>
				<td>
				" . $row['Daycare'] . "</td>
				<td>
				" . $row['BeforeDeviation'] . "%</td>
				<td>
				" . $row['AfterDeviation'] . "%</td>";
		$checkDate = $startDate;
		$workingday = 0;
		$attendance = 0;
		while ($checkDate <= $endDate) {
			$sqlhols1 = "Select count(*) from holiday where Date='$checkDate'";
			$resulthols1 = mysql_query($sqlhols1);
			while ($rowhol = mysql_fetch_array($resulthols1)) {
				if ($rowhol['count(*)'] == 0) {
					$day = date('l', strtotime($checkDate));
					if ($day != 'Sunday' && $day != 'Saturday') {
						$sqlattendance = "Select * from attendancerecord where date(SignInTimeStamp)='$checkDate' and NRIC='" . $row['NRIC'] . "' and $where";
						$checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
						$workingday++;
						$resultattendance = mysql_query($sqlattendance);
						if (mysql_num_rows($resultattendance) == 1) {
							echo "<td>1</td>";
							$attendance++;
						}
						else
							echo "<td>0</td>";
					}
					else
						$checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
				} else {
					$checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
				}
			}
		}
		$ttlattendance += $attendance;
		$avg = $ttlattendance / $sno;
		echo "<td>" . $attendance . "</td>";
		echo"</tr>";
		$sno++;

		//Before Deviation
		if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 100) {
			$beforeDeviationSC100 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 95) {
			$beforeDeviationSC95 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 90) {
			$beforeDeviationSC90 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 85) {
			$beforeDeviationSC85 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 80) {
			$beforeDeviationSC80 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 75) {
			$beforeDeviationSC75 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 70) {
			$beforeDeviationSC70 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 65) {
			$beforeDeviationSC65 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 60) {
			$beforeDeviationSC60 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 55) {
			$beforeDeviationSC55 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 50) {
			$beforeDeviationSC50 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 45) {
			$beforeDeviationSC45 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 40) {
			$beforeDeviationSC40 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 35) {
			$beforeDeviationSC35 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 30) {
			$beforeDeviationSC30 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 25) {
			$beforeDeviationSC25 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 20) {
			$beforeDeviationSC20 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 15) {
			$beforeDeviationSC15 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 10) {
			$beforeDeviationSC10 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 5) {
			$beforeDeviationSC5 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 0) {
			$beforeDeviationSC0 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 100) {
			$beforeDeviationPR100 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 95) {
			$beforeDeviationPR95 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 90) {
			$beforeDeviationPR90 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 85) {
			$beforeDeviationPR85 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 80) {
			$beforeDeviationPR80 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 75) {
			$beforeDeviationPR75 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 70) {
			$beforeDeviationPR70 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 65) {
			$beforeDeviationPR65 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 60) {
			$beforeDeviationPR60 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 55) {
			$beforeDeviationPR55 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 50) {
			$beforeDeviationPR50 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 45) {
			$beforeDeviationPR45 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 40) {
			$beforeDeviationPR40 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 35) {
			$beforeDeviationPR35 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 30) {
			$beforeDeviationPR30 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 25) {
			$beforeDeviationPR25 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 20) {
			$beforeDeviationPR20 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 15) {
			$beforeDeviationPR15 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 10) {
			$beforeDeviationPR10 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 5) {
			$beforeDeviationPR5 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 0) {
			$beforeDeviationPR0 += $attendance;
		}

		//After Deviation
		if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 100) {
			$afterDeviationPR100 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 95) {
			$afterDeviationPR95 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 90) {
			$afterDeviationPR90 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 85) {
			$afterDeviationPR85 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 80) {
			$afterDeviationPR80 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 75) {
			$afterDeviationPR75 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 70) {
			$afterDeviationPR70 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 65) {
			$afterDeviationPR65 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 60) {
			$afterDeviationPR60 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 55) {
			$afterDeviationPR55 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 50) {
			$afterDeviationPR50 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 45) {
			$afterDeviationPR45 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 40) {
			$afterDeviationPR40 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 35) {
			$afterDeviationPR35 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 30) {
			$afterDeviationPR30 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 25) {
			$afterDeviationPR25 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 20) {
			$afterDeviationPR20 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 15) {
			$afterDeviationPR15 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 10) {
			$afterDeviationPR10 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 5) {
			$afterDeviationPR5 += $attendance;
		} else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 0) {
			$afterDeviationPR0 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 100) {
			$afterDeviationSC100 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 95) {
			$afterDeviationSC95 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 90) {
			$afterDeviationSC90 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 85) {
			$afterDeviationSC85 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 80) {
			$afterDeviationSC80 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 75) {
			$afterDeviationSC75 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 70) {
			$afterDeviationSC70 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 65) {
			$afterDeviationSC65 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 60) {
			$afterDeviationSC60 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 55) {
			$afterDeviationSC55 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 50) {
			$afterDeviationSC50 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 45) {
			$afterDeviationSC45 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 40) {
			$afterDeviationSC40 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 35) {
			$afterDeviationSC35 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 30) {
			$afterDeviationSC30 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 25) {
			$afterDeviationSC25 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 20) {
			$afterDeviationSC20 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 15) {
			$afterDeviationSC15 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 10) {
			$afterDeviationSC10 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 5) {
			$afterDeviationSC5 += $attendance;
		} else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 0) {
			$afterDeviationSC0 += $attendance;
		}
	} echo"</table>
			</div>
			</p>
			<h4>Average Attendance: " . round($avg, 5) . "</h4>
			";
	echo"
			<p></p>
			<p></p>                                                                                      
			<table width='100%'  style='vertical-align:middle;text-align:center;'>
	<tr>
	<td width='50%'>
	<div class='table' align='center'>
			<table>
			<tr>
			<td colspan='6'style='vertical-align:middle;text-align:center;'>Before Deviation
			</td>
			</tr>
			<tr>
			<td>Subsidy Level</td>
			<td>SC</td>
			<td>Average</td>
			<td>Subsidy Level</td>
			<td>PR</td>
			<td>Average</td>
			</tr>";

	for ($i = 100; $i >= 0; $i-=5) {

		echo "<tr>
			<td>" . $i . "%</td>";
		$sqlbdsc = "SELECT count(*) from client where BeforeDeviation=$i and Citizenship='SC' and $where";
		$sqlbdpr = "SELECT count(*) from client where BeforeDeviation=$i and Citizenship='PR' and $where";
		$resultbdsc = mysql_query($sqlbdsc);
		$resultbdpr = mysql_query($sqlbdpr);

		while ($rowbdsc = mysql_fetch_array($resultbdsc)) {
			if ($rowbdsc['count(*)'] == 0) {
				echo "<td>0</td>
					<td>0</td>";
			} else {
				echo "<td>" . $rowbdsc['count(*)'] . "</td>";

				$countBeforeDeviationSC +=$rowbdsc['count(*)'];

				if ($i == 100) {
					$average = round($beforeDeviationSC100 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 95) {
					$average = round($beforeDeviationSC95 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 90) {
					$average = round($beforeDeviationSC90 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 85) {
					$average = round($beforeDeviationSC85 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 80) {
					$average = round($beforeDeviationSC80 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 75) {
					$average = round($beforeDeviationSC75 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 70) {
					$average = round($beforeDeviationSC70 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 65) {
					$average = round($beforeDeviationSC65 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 60) {
					$average = round($beforeDeviationSC60 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 55) {
					$average = round($beforeDeviationSC55 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 50) {
					$average = round($beforeDeviationSC50 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 45) {
					$average = round($beforeDeviationSC45 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 40) {
					$average = round($beforeDeviationSC40 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 35) {
					$average = round($beforeDeviationSC35 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 30) {
					$average = round($beforeDeviationSC30 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 25) {
					$average = round($beforeDeviationSC25 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 20) {
					$average = round($beforeDeviationSC20 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 15) {
					$average = round($beforeDeviationSC15 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 10) {
					$average = round($beforeDeviationSC10 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 5) {
					$average = round($beforeDeviationSC5 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;
				} else if ($i == 0) {
					$average = round($beforeDeviationSC0 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAverageSC +=$average;

				}
			}
		}

		echo "
			<td>" . $i . "%</td>";

		while ($rowbdpr = mysql_fetch_array($resultbdpr)) {
			if ($rowbdpr['count(*)'] == 0) {
				echo "<td>0</td>
					<td>0</td>";
			} else {
				echo "<td>" . $rowbdpr['count(*)'] . "</td>";

				$countBeforeDeviationPR +=$rowbdpr['count(*)'];

				if ($i == 100) {
					$average = round($beforeDeviationPR100 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 95) {
					$average = round($beforeDeviationPR95 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 90) {
					$average = round($beforeDeviationPR90 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 85) {
					$average = round($beforeDeviationPR85 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 80) {
					$average = round($beforeDeviationPR80 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 75) {
					$average = round($beforeDeviationPR75 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 70) {
					$average = round($beforeDeviationPR70 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 65) {
					$average = round($beforeDeviationPR65 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 60) {
					$average = round($beforeDeviationPR60 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 55) {
					$average = round($beforeDeviationPR55 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 50) {
					$average = round($beforeDeviationPR50 / $workingday, 5);

					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 45) {
					$average = round($beforeDeviationPR45 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 40) {
					$average = round($beforeDeviationPR40 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 35) {
					$average = round($beforeDeviationPR35 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 30) {
					$average = round($beforeDeviationPR30 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 25) {
					$average = round($beforeDeviationPR25 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 20) {
					$average = round($beforeDeviationPR20 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 15) {
					$average = round($beforeDeviationPR15 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 10) {
					$average = round($beforeDeviationPR10 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 5) {
					$average = round($beforeDeviationPR5 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				} else if ($i == 0) {
					$average = round($beforeDeviationPR0 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$beforeDeviationTotalAveragePR +=$average;
				}
			}
		}
		

		echo"</tr>";
		
	}
	echo "<td> Total </td>
			<td>". $countBeforeDeviationSC. "</td>
				<td>" . $beforeDeviationTotalAverageSC . "</td>
				<td>Total</td>
					<td>" . $countBeforeDeviationPR ."</td>
						<td>" . $beforeDeviationTotalAveragePR . "</td>";

	echo"
			</table></div>
			<td>
			<td width='50%'>
			<div class='table' align='left'>
			<table>
			<tr>
			<td colspan='6' style='vertical-align:middle;text-align:center;'>After Deviation</td>
			</tr>
			<tr>
			<td>Subsidy Level</td>
			<td>SC</td>
			<td>Average</td>
			<td>Subsidy Level</td>
			<td>PR</td>
			<td>Average</td>
			</tr>
			";
	for ($j = 100; $j >= 0; $j-=5) {

		echo "<tr>
			<td>" . $j . "%</td>";
		$sqladsc = "SELECT count(*) from client where AfterDeviation=$j and Citizenship='SC' and $where";
		$sqladpr = "SELECT count(*) from client where AfterDeviation=$j and Citizenship='PR' and $where";
		$resultadsc = mysql_query($sqladsc);
		$resultadpr = mysql_query($sqladpr);

		while ($rowadsc = mysql_fetch_array($resultadsc)) {
			if ($rowadsc['count(*)'] == 0) {
				echo "<td>0</td>
					<td>0</td>";
			} else {
				echo "<td>" . $rowadsc['count(*)'] . "</td>";

				$countAfterDeviationSC +=$rowadsc['count(*)'];

				if ($j == 100) {
					$average = round($afterDeviationSC100 / $workingday, 5);
					echo "<td>" . $afterDeviationSC100 . $workingday . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 95) {
					$average = round($afterDeviationSC95 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 90) {
					$average = round($afterDeviationSC90 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 85) {
					$average = round($afterDeviationSC85 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 80) {
					$average = round($afterDeviationSC80 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 75) {
					$average = round($afterDeviationSC75 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 70) {
					$average = round($afterDeviationSC70 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 65) {
					$average = round($afterDeviationSC65 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 60) {
					$average = round($afterDeviationSC60 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 55) {
					$average = round($afterDeviationSC55 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 50) {
					$average = round($afterDeviationSC50 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 45) {
					$average = round($afterDeviationSC45 / $workingday, 5);

					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 40) {
					$average = round($afterDeviationSC40 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 35) {
					$average = round($afterDeviationSC35 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 30) {
					$average = round($afterDeviationSC30 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 25) {
					$average = round($afterDeviationSC25 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 20) {
					$average = round($afterDeviationSC20 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 15) {
					$average = round($afterDeviationSC15 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 10) {
					$average = round($afterDeviationSC10 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 5) {
					$average = round($afterDeviationSC5 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				} else if ($j == 0) {
					$average = round($afterDeviationSC0 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAverageSC += $average;
				}
			}
		}

		echo "
			<td>" . $j . "%</td>";

		while ($rowadpr = mysql_fetch_array($resultadpr)) {
			if ($rowadpr['count(*)'] == 0) {
				echo "<td>0</td>
					<td>0</td>";
			} else {
				echo "<td>" . $rowadpr['count(*)'] . "</td>";

				$countAfterDeviationPR +=$rowadpr['count(*)'];

				if ($j == 100) {
					$average = round($afterDeviationPR100 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 95) {
					$average = round($afterDeviationPR95 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 90) {
					$average = round($afterDeviationPR90 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 85) {
					$average = round($afterDeviationPR85 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 80) {
					$average = round($afterDeviationPR80 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 75) {
					$average = round($afterDeviationPR75 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 70) {
					$average = round($afterDeviationPR70 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 65) {
					$average = round($afterDeviationPR65 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 60) {
					$average = round($afterDeviationPR60 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 55) {
					$average = round($afterDeviationPR55 / $workingday, 5);

					echo "<td>" . $average . "</td>";

					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 50) {
					$average = round($afterDeviationPR50 / $workingday, 5);

					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 45) {
					$average = round($afterDeviationPR45 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 40) {
					$average = round($afterDeviationPR40 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 35) {
					$average = round($afterDeviationPR35 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 30) {
					$average = round($afterDeviationPR30 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 25) {
					$average = round($afterDeviationPR25 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 20) {
					$average = round($afterDeviationPR20 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 15) {
					$average = round($afterDeviationPR15 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 10) {
					$average = round($afterDeviationPR10 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 5) {
					$average = round($afterDeviationPR5 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				} else if ($j == 0) {
					$average = round($afterDeviationPR0 / $workingday, 5);
					echo "<td>" . $average . "</td>";
					$afterDeviationTotalAveragePR += $average;
				}
			}
		}	
		echo"</tr>";	
	}
	echo "<td> Total </td>
		<td>". $countAfterDeviationSC. "</td>
		<td>" . $afterDeviationTotalAverageSC . "</td>
		<td>Total</td>
		<td>" . $countAfterDeviationPR ."</td>
		<td>" . $afterDeviationTotalAveragePR . "</td>";

	echo "</table></div></td></tr></table></p>
		<br>";

	//close connection
	$sqlDBManager->close_connect();
?>